﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DataAccess;
using DAO;
using System.Data;
namespace BUL
{
    public class DivisionBUL
    {
        ConnectDatabase cd = new ConnectDatabase();
        public DataTable load(String date) {
            String sql = @"select idDivision as 'Mã PC',division.idStaff as 'Mã nv',staff.fullname as 'Tên nv',staff.position as 'Chức vụ',clinic.name as 'Phòng khám' ,ward.name as 'Phòng bệnh'  
                    from division,staff,clinic,ward where division.idStaff = staff.idStaff and division.idClinic = clinic.idClinic and division.idWard = ward.idWard and division.date like '%"+date+"%'";
            return cd.ExcuteQuery(sql);
        }
        public DataTable load1(String idWard)
        {
            String sql = @"select division.idStaff,staff.fullname,staff.phone,ward.name  
                    from division,staff,ward where division.idStaff = staff.idStaff and division.idWard = ward.idWard and division.date like '%" + DateTime.Now.ToString("yyyy-MM-dd") + "%' and division.idWard='"+idWard+"' ";
            return cd.ExcuteQuery(sql);
        }
        public String getId(String idStaff) {
         String sql =   @"select idDivision
                    from division where date like '%" + DateTime.Now.ToString("yyyy-MM-dd") + "%' and idStaff='" + idStaff + "' ";
            return cd.ExcuteQuery(sql).Rows[0][0].ToString();

        }
        public DataTable getAllById(String idDivision) {
            String sql = @"select *
                    from division where idDivision='" + idDivision + "' ";
            return cd.ExcuteQuery(sql);
        }
        public Boolean insert(DivisionDAO p)
        {
            String sql = @"insert into division (date,idStaff,idClinic,idWard) values(@date,@idStaff,@idClinic,@idWard)";
            int count = 4;
            String[] name = new String[count];
            object[] value = new object[count];
            name[0] = "@date";
            name[1] = "@idStaff";
            name[2] = "@idClinic";
            name[3] = "@idWard";
            value[0] = p.Date;
            value[1] = p.IdStaff;
            value[2] = p.IdClinic;
            value[3] = p.IdWard;
            return cd.ExcuteNonQuery(sql, name, value, count);
        }
        public Boolean update(DivisionDAO p)
        {
            String sql = @"update division set idStaff=@idStaff, idClinic=@idClinic,idWard=@idWard where  idDivision=@id ";
            int count = 4;
            String[] name = new String[count];
            object[] value = new object[count];
            name[0] = "@idStaff";
            name[1] = "@idClinic";
            name[2] = "@idWard";
            name[3] = "@id";
            value[0] = p.IdStaff;
            value[1] = p.IdClinic;
            value[2] = p.IdWard;
            value[3] = p.IdDivision;
            return cd.ExcuteNonQuery(sql, name, value, count);
        }
        public Boolean delete(String id)
        {
            String sql = @"delete from division where idDivision = '" + id + "'";
            return cd.ExcuteNonQuery(sql);
        }
    }
}
